﻿/*
 * @file: BouquetDA.cs
 * @in namespace: DAL
 */
using System;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class BouquetDA : BaseDAL, IBouquet
    {
        private const string tableName = "Bouquets";
        private const string bouquetID = "BouquetID";
        private const string occasionID = "OccasionID";
        private const string name = "Name";
        private const string description = "Description";
        private const string price = "Price";
        private const string quantity = "Quantity";
        private const string isEnable = "IsEnable";

        public BouquetDA()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        /*
         * @method: BouquetNameExisted
         * @description: check wheather bouquet name exist or not
         * @input: BouquetName
         * @output: 1 if existed
         */
        public static int BouquetNameExisted(string _bouquetName) {
            try
            {
                return RecordExisted(tableName, name, _bouquetName);
            }
            catch (Exception ex)
            {
                throw ex;
            }   
        }

        #region IBouquet Members

        /*
         * @method: GetBouquets
         * @description: get all Bouquets
         * @input: allowFilter, TRUE enables ignore hidden records
         * @output: all records corresponding
         */
        public Bouquet[] GetBouquets(bool allowFilter)
        {
            Bouquet[] result = null;
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                if(allowFilter)
                    cmd.CommandText = String.Format("SELECT * FROM {0} WHERE {1} = 1", 
                        tableName, isEnable);
                else
                    cmd.CommandText = String.Format("SELECT * FROM {0}", tableName);

                String[] columnNames = {    bouquetID, 
                                            occasionID,
                                            name,
                                            description,
                                            price,
                                            quantity,
                                            isEnable};
                result = SelectCollection<Bouquet>(columnNames, columnNames, cmd);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /*
         * @method: GetBouquetByOccasionID
         * @description: get all Bouquets by OccasionID
         * @input: OccasionID, allowFilter <TRUE enables ignore hidden records>
         * @output: all records corresponding
         */
        public Bouquet[] GetBouquetByOccasionID(int _occasionID, bool allowFilter)
        {
            Bouquet[] result = null;
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                if (allowFilter)
                {
                    cmd.CommandText = String.Format("SELECT * FROM {0} WHERE {1} = @{2} AND {3} = 1", 
                        tableName, occasionID, occasionID, isEnable);
                    cmd.Parameters.Add(String.Format("@{0}", occasionID), SqlDbType.Int).Value = _occasionID;
                }
                else
                {
                    cmd.CommandText = String.Format("SELECT * FROM {0} WHERE {1} = @{2}", 
                        tableName, occasionID, occasionID);
                    cmd.Parameters.Add(String.Format("@{0}", occasionID), SqlDbType.Int).Value = _occasionID;
                }
                    
                String[] columnNames = {    bouquetID, 
                                            occasionID,
                                            name,
                                            description,
                                            price,
                                            quantity,
                                            isEnable};
                result = SelectCollection<Bouquet>(columnNames, columnNames, cmd);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

       /*
        * @method: GetBouquetByBouquetID
        * @description: get all Bouquets by BouquetID
        * @input: BouquetID, allowFilter <TRUE enables ignore hidden records>
        * @output: all records corresponding
        */
        public Bouquet GetBouquetByBouquetID(int _bouquetID, bool allowFilter)
        {
            Bouquet[] result = null;
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                if (allowFilter)
                {
                    cmd.CommandText = String.Format("SELECT * FROM {0} WHERE {1} = @{2} AND {3} = 1", 
                        tableName, bouquetID, bouquetID, isEnable);
                    cmd.Parameters.Add(String.Format("@{0}", bouquetID), SqlDbType.Int).Value = _bouquetID;
                }
                else
                {
                    cmd.CommandText = String.Format("SELECT * FROM {0} WHERE {1} = @{2}", 
                        tableName, bouquetID, bouquetID);
                    cmd.Parameters.Add(String.Format("@{0}", bouquetID), SqlDbType.Int).Value = _bouquetID;
                }

                String[] columnNames = {    bouquetID, 
                                            occasionID,
                                            name,
                                            description,
                                            price,
                                            quantity,
                                            isEnable};
                result = SelectCollection<Bouquet>(columnNames, columnNames, cmd);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            if(result.Length > 0)
                return result[0];
            return null;
        }

        /*
         * @method: InsertBouquet
         * @description: insert new bouquet to database
         * @input: Bouquet entity
         * @output: 1 if insert successful, 0 if error occurred
         */
        public int InsertBouquet(Bouquet _bouquet, out int autoNumber)
        {
            int result = 0;
            try
            {
                String[] columnNames = {    occasionID, 
                                            name, 
                                            description,
                                            price,
                                            quantity};

                Object[] values = { _bouquet.OccasionID,
                                    _bouquet.Name, 
                                    _bouquet.Description,
                                    _bouquet.Price,
                                    _bouquet.Quantity};

                result = BaseDAL.InsertTableWithReturnID(tableName, columnNames, values, out autoNumber);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /*
         * @method: DisableBouquet
         * @description: hidden a bouquet
         * @input: BouquetID, isEnable <FALSE if hidden>
         * @output: 1 if update successful, 0 if error occurred
         */
        public int DisableBouquet(int _bouquetID, bool _isEnable)
        {
            int result = 0;
            try
            {
                String[] columnNames = { isEnable };
                Object[] values = { _isEnable };
                String[] keyColumnNames = { bouquetID };
                Object[] keyColumnValues = { _bouquetID };
                result = UpdateTable(tableName, columnNames, values, keyColumnNames, keyColumnValues);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /*
         * @method: UpdateBouquet
         * @description: update Bouquet information
         * @input: Bouquet entity
         * @output: 1 if successful, 0 if error occurred
         */
        public int UpdateBouquet(Bouquet _bouquet)
        {
            int result = 0;
            try
            {
                String[] columnNames = {    occasionID,
                                            name,
                                            description,
                                            price,
                                            quantity};
                Object[] values = { _bouquet.OccasionID,
                                    _bouquet.Name,
                                    _bouquet.Description,
                                    _bouquet.Price,
                                    _bouquet.Quantity};
                String[] keyColumnNames = { bouquetID };
                Object[] keyColumnValues = { _bouquet.BouquetID };
                result = UpdateTable(tableName, columnNames, values, keyColumnNames, keyColumnValues);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        #endregion
    }
}
